import pandas as pd
import os

def convert_to_numeric(series):
    return pd.to_numeric(series, errors='coerce')

def process_worksheet(sheet_data, sheet_name):
    # Convert  columns to numeric first
    sheet_data.iloc[:, 1] = convert_to_numeric(sheet_data.iloc[:, 1])  
    sheet_data.iloc[:, 7] = convert_to_numeric(sheet_data.iloc[:, 7])  
    sheet_data.iloc[:, 9] = convert_to_numeric(sheet_data.iloc[:, 9])  
    
    # First DataFrame
    df1 = pd.DataFrame({
        '时间[min]': sheet_data.iloc[4:, 0],  
        '信号[mV]': sheet_data.iloc[4:, 1] / 100000  
    })
    
    # Second DataFrame
    valid_rows = (sheet_data.iloc[4:, 4].notna() &  
                 sheet_data.iloc[4:, 7].notna() &  
                 sheet_data.iloc[4:, 9].notna())  
    
    # Create sequence
    peak_sequence = range(1, sum(valid_rows) + 1)
    
    df2 = pd.DataFrame({
        '峰序': peak_sequence,
        '保留时间[Min]': sheet_data.iloc[4:, 4][valid_rows].reset_index(drop=True)/100000,
        '峰高': sheet_data.iloc[4:, 9][valid_rows].reset_index(drop=True)/100000,
        '峰面积': sheet_data.iloc[4:, 7][valid_rows].reset_index(drop=True)/100000
    })
    
    # Add '图谱采样' at the top
    output = '指纹图谱采样信号数据\n'
    # Add first dataframe
    output += df1.to_string(index=False, 
                           float_format=lambda x: '{:.5f}'.format(x) if pd.notnull(x) else '',
                           na_rep='')  # Replace NA with empty string
    # Add '图谱积分' after first dataframe
    output += '\n指纹图谱积分数据\n'
    # Add second dataframe
    output += df2.to_string(index=False, 
                           float_format=lambda x: '{:.5f}'.format(x) if pd.notnull(x) else '',
                           na_rep='')  # Replace NA with empty string
    
    # Write to txt file
    output_filename = f"{sheet_name}.txt"
    with open(output_filename, 'w', encoding='utf-8') as f:
        f.write(output)
    
    return output_filename

def process_excel_file(excel_file):
    # Read Excel file
    xl = pd.ExcelFile(excel_file)
    
    # Process each worksheet
    processed_files = []
    for sheet_name in xl.sheet_names:
        try:
            # Read the worksheet
            sheet_data = pd.read_excel(excel_file, sheet_name=sheet_name)
            
            # Process the worksheet and get output filename
            output_file = process_worksheet(sheet_data, sheet_name)
            processed_files.append(output_file)
            
            print(f"Successfully processed worksheet: {sheet_name}")
        except Exception as e:
            print(f"Error processing worksheet {sheet_name}: {str(e)}")
    
    return processed_files



excel_file = "文件1.xlsx" 
output_files = process_excel_file(excel_file)
print("\nGenerated text files:")
for file in output_files:
    print(f"- {file}")
